/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package modelo.helper;

import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import modelo.DTO.DTODataMining;
import modelo.DTO.DTOVisitasAlumno;
import modelo.HibernateUtil;
import modelo.MdlLog;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;


/**
 *
 * @version 1.1
 * @author Ion B. Padilla
 */
public class SequencesHelper {
    private static SequencesHelper instance;

    public SequencesHelper() {
    }

    public static SequencesHelper getInstance() {
        if (null == instance) {
            instance = new SequencesHelper();
        }
        return instance;
    }

    /**
     * Metodo para conseguir el numero de eventos que se han producido en los recursos entre dos fechas (NO USADO)
     * @param fInicio Fecha de inicio
     * @param fFin Fecha de fin
     * @param userId identificador del usuario
     */
    @SuppressWarnings("unchecked")
    public ArrayList<MdlLog> getLogFromDates(Date fInicio, Date fFin, Long userId) {
        Transaction tx = null;
        ArrayList<MdlLog> logs;
        try {
           // Session session = HibernateUtil.getSessionFactory().getCurrentSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Query q = HibernateUtil.getInstance().getSession().createQuery("from MdlLog where cmid!=0 and time between " + fInicio.getTime() / 1000 + " and " + fFin.getTime() / 1000 + " and userid=" + userId);
            if (q.list().size() != 0) {
                logs = (ArrayList<MdlLog>) q.list();
                tx.commit(); session.close();
                return logs;
            } else {
                tx.commit(); session.close();
                return null;
            }
        } catch (HibernateException e) {
            e.getStackTrace();
            return null;
        }
    }
    /**
     * Metodo para conseguir los logins de los alumnos
     */
    public ArrayList<DTODataMining> getLoginsPorAlumnos (){
        Transaction tx = null;
        ArrayList<DTODataMining> lista = new ArrayList<DTODataMining>();
        try {
            //Session session = HibernateUtil.getSessionFactory().getCurrentSession();
          //  Session session = HibernateUtil.getSessionFactory().openSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Iterator results = session.createQuery(
                    "SELECT u.username AS username, COUNT(l.id) AS quantity "
                    + "FROM MdlLog l, MdlUser u "
                    + "WHERE u.id=l.userid "
                    + "AND l.action='login' "
                    + "GROUP BY l.userid "
                    + "ORDER BY COUNT(l.id) ").list().iterator();
            while (results.hasNext()) {
                Object[] row = (Object[]) results.next();
                DTOVisitasAlumno alumno = new DTOVisitasAlumno((String) row[0], Long.toString((Long) row[1]));
                lista.add(alumno);
            }
            tx.commit(); session.close();
            return lista;
        } catch (HibernateException e) {
            System.out.println(e.getMessage());
            e.getStackTrace();
            return null;
        }
    }
    /**
     * Metodo para conseguir los logins de los alumnos por curso
     * @param curso curso al que vamos a consultar
     */
    public ArrayList<DTODataMining> getLoginsPorAlumnosPorCurso (int curso){
        Transaction tx = null;
        ArrayList<DTODataMining> lista = new ArrayList<DTODataMining>();
        try {
            //Session session = HibernateUtil.getSessionFactory().getCurrentSession();
           // Session session = HibernateUtil.getSessionFactory().openSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Iterator results = session.createQuery(
                    "SELECT u.username AS username, COUNT(l.id) AS quantity "
                    + "FROM MdlLog l, MdlUser u "
                    + "WHERE u.id=l.userid "
                    + "AND l.course="+curso+" "
                    + "AND l.action='login' "
                    + "GROUP BY l.userid "
                    + "ORDER BY COUNT(l.id) ").list().iterator();
            while (results.hasNext()) {
                Object[] row = (Object[]) results.next();
                DTOVisitasAlumno alumno = new DTOVisitasAlumno((String) row[0], Long.toString((Long) row[1]));
                lista.add(alumno);
            }
            tx.commit(); session.close();
            return lista;
        } catch (HibernateException e) {
            System.out.println(e.getMessage());
            e.getStackTrace();
            return null;
        }
    }
    /**
     * Metodo para conseguir los logins de los alumnos por alumno
     * @param nombre nombre del alumno
     */
    public DTODataMining getLoginsPorAlumno(String nombre) {
        DTOVisitasAlumno dtoVisitas = null;
        Transaction tx = null;
        try {
            //Session session = HibernateUtil.getSessionFactory().getCurrentSession();
            //Session session = HibernateUtil.getSessionFactory().openSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Iterator results = session.createQuery(
                    "SELECT u.username AS username, COUNT(l.id) AS quantity "
                    + "FROM MdlLog l, MdlUser u "
                    + "WHERE u.id=l.userid "
                    + "AND l.action='login' "
                    + "AND u.username='" + nombre + "' "
                    + "GROUP BY l.userid "
                    + "ORDER BY COUNT(l.id) ").list().iterator();
            while (results.hasNext()) {
                Object[] row = (Object[]) results.next();
                dtoVisitas = new DTOVisitasAlumno((String) row[0], Long.toString((Long) row[1]));
            }
            tx.commit(); session.close();
            return dtoVisitas;
        } catch (HibernateException e) {
            System.out.println(e.getMessage());
            e.getStackTrace();
            return null;
        }
    }
    /**
     * Metodo para conseguir los logins de los alumnos por alumno y por curso
     * @param nombre nombre del alumno
     * @param curso curso en el que vamos a consultar
     */
    public DTODataMining getLoginsPorAlumnoPorCurso(String nombre,int curso) {
        DTOVisitasAlumno dtoVisitas = null;
        Transaction tx = null;
        try {
            //Session session = HibernateUtil.getSessionFactory().getCurrentSession();
            //Session session = HibernateUtil.getSessionFactory().openSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Iterator results = session.createQuery(
                    "SELECT u.username AS username, COUNT(l.id) AS quantity "
                    + "FROM MdlLog l, MdlUser u "
                    + "WHERE u.id=l.userid "
                    + "AND l.action='login' "
                    + "AND u.username='" + nombre + "' "
                    + "And l.course="+curso+" "
                    + "GROUP BY l.userid "
                    + "ORDER BY COUNT(l.id) ").list().iterator();
            while (results.hasNext()) {
                Object[] row = (Object[]) results.next();
                dtoVisitas = new DTOVisitasAlumno((String) row[0], Long.toString((Long) row[1]));
            }
            tx.commit(); session.close();
            return dtoVisitas;
        } catch (HibernateException e) {
            System.out.println(e.getMessage());
            e.getStackTrace();
            return null;
        }
    }
    /**
     * Metodo para conseguir la variable de secuencia de los alumnos por curso
     * @param nombre nombre del alumno
     * @param curso curso en el que vamos a consultar
     */
    public ArrayList<DTODataMining> getInteraccionesPorSecuenciaAlumnos (int curso, ArrayList<String> alumnos){
        Transaction tx = null;
        Iterator iterador=alumnos.iterator();
        String nombre;
        ArrayList<DTODataMining> listaResultante = new ArrayList<DTODataMining>();
        Object[] row;
        int diferencia=0;
        try {
            while (iterador.hasNext()){
                nombre=(String)iterador.next();
              //  Session session = HibernateUtil.getSessionFactory().openSession();
                Session session = HibernateUtil.getInstance().getSession();
                tx = session.beginTransaction();
                Iterator results = session.createQuery(
                        "SELECT r.id as numeroRecursos  "
                        + "FROM MdlResource r ,MdlCourseModules cm ,MdlModules modu, MdlLog log , MdlUser u "
                        + "WHERE modu.name = 'resource'  "
                        + "AND cm.instance=r.id  "
                        + "AND modu.id=cm.module  "
                        + "AND cm.id=log.cmid  "
                        + "AND log.userid=u.id "
                        + "AND log.course="+curso+" "
                        + "AND u.username='"+nombre+"' "
                        + "AND cmid!=0  "
                        + "ORDER BY log.time").list().iterator();
                while (results.hasNext()) {
                 //  diferencia=(int) Math.abs(diferencia-(Long)results.next());
                    diferencia++;
                    results.next();
                }
                listaResultante.add(new DTOVisitasAlumno(nombre,Integer.toString(diferencia)));
                diferencia=0;
                session.close();
            }
            return listaResultante;
        } catch (HibernateException e) {
            System.out.println(e.getMessage());
            e.getStackTrace();
            return null;
        }
    }
}